/****** Object:  View [dbo].[vw_aspnet_WebPartState_User]    Script Date: 09/27/2007 23:53:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_WebPartState_User]
  AS SELECT [dbo].[aspnet_PersonalizationPerUser].[PathId], [dbo].[aspnet_PersonalizationPerUser].[UserId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationPerUser].[PageSettings]), [dbo].[aspnet_PersonalizationPerUser].[LastUpdatedDate]
  FROM [dbo].[aspnet_PersonalizationPerUser]
GO
/****** Object:  View [dbo].[vw_aspnet_WebPartState_Shared]    Script Date: 09/27/2007 23:53:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_WebPartState_Shared]
  AS SELECT [dbo].[aspnet_PersonalizationAllUsers].[PathId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationAllUsers].[PageSettings]), [dbo].[aspnet_PersonalizationAllUsers].[LastUpdatedDate]
  FROM [dbo].[aspnet_PersonalizationAllUsers]
GO
/****** Object:  View [dbo].[vw_page_list]    Script Date: 09/27/2007 23:53:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_page_list]
AS
SELECT     page.pageid, page.kbid, page.createdate, page.publishdate, page.expiredate, page.statusdate, page.currentuserid, page.title, page.iv_access_type, 
                      page.iv_level, page.iv_status, page.iv_priority, page.keywords, 
                      page.pagerevision, page.iv_itemtype, iv1.longdesc AS accesstype, iv2.longdesc AS difficultylevel, iv3.longdesc AS status, iv4.longdesc AS priority, 
                      iv5.longdesc AS itemtype, summ.summary, page.templateid, page.spotlight, u.UserName, page.userid
FROM         dbo.page AS page LEFT OUTER JOIN
                      dbo.page_summary AS summ ON summ.pageid = page.pageid LEFT OUTER JOIN
                      dbo.int_value AS iv1 ON iv1.int_value = page.iv_access_type AND iv1.int_name = 'iv_access_type' LEFT OUTER JOIN
                      dbo.int_value AS iv2 ON iv2.int_value = page.iv_level AND iv2.int_name = 'iv_level' LEFT OUTER JOIN
                      dbo.int_value AS iv3 ON iv3.int_value = page.iv_status AND iv3.int_name = 'iv_status' LEFT OUTER JOIN
                      dbo.int_value AS iv4 ON iv4.int_value = page.iv_priority AND iv4.int_name = 'iv_priority' LEFT OUTER JOIN
                      dbo.int_value AS iv5 ON iv5.int_value = page.iv_itemtype AND iv5.int_name = 'iv_itemtype' LEFT OUTER JOIN
                      dbo.aspnet_Users AS u ON u.UserId = page.currentuserid
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1[50] 2[25] 3) )"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4[30] 2[40] 3) )"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2[66] 3) )"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 5
   End
   Begin DiagramPane = 
      PaneHidden = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "page"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 121
               Right = 207
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "summ"
            Begin Extent = 
               Top = 6
               Left = 245
               Bottom = 91
               Right = 397
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "iv1"
            Begin Extent = 
               Top = 6
               Left = 435
               Bottom = 121
               Right = 587
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "iv2"
            Begin Extent = 
               Top = 6
               Left = 625
               Bottom = 121
               Right = 777
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "iv3"
            Begin Extent = 
               Top = 96
               Left = 245
               Bottom = 211
               Right = 397
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "iv4"
            Begin Extent = 
               Top = 126
               Left = 38
               Bottom = 241
               Right = 190
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "iv5"
            Begin Extent = 
               Top = 126
               Left = 435
               Bottom = 241
               Right = 587
            End
            DisplayFlags = 280
           ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_page_list'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N' TopColumn = 0
         End
         Begin Table = "u"
            Begin Extent = 
               Top = 126
               Left = 625
               Bottom = 241
               Right = 796
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "o"
            Begin Extent = 
               Top = 6
               Left = 815
               Bottom = 121
               Right = 984
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      PaneHidden = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_page_list'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_page_list'
GO
/****** Object:  View [dbo].[vw_aspnet_MembershipUsers]    Script Date: 09/27/2007 23:53:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_MembershipUsers]
  AS SELECT [dbo].[aspnet_Membership].[UserId],
            [dbo].[aspnet_Membership].[PasswordFormat],
            [dbo].[aspnet_Membership].[MobilePIN],
            [dbo].[aspnet_Membership].[Email],
            [dbo].[aspnet_Membership].[LoweredEmail],
            [dbo].[aspnet_Membership].[PasswordQuestion],
            [dbo].[aspnet_Membership].[PasswordAnswer],
            [dbo].[aspnet_Membership].[IsApproved],
            [dbo].[aspnet_Membership].[IsLockedOut],
            [dbo].[aspnet_Membership].[CreateDate],
            [dbo].[aspnet_Membership].[LastLoginDate],
            [dbo].[aspnet_Membership].[LastPasswordChangedDate],
            [dbo].[aspnet_Membership].[LastLockoutDate],
            [dbo].[aspnet_Membership].[FailedPasswordAttemptCount],
            [dbo].[aspnet_Membership].[FailedPasswordAttemptWindowStart],
            [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptCount],
            [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptWindowStart],
            [dbo].[aspnet_Membership].[Comment],
            [dbo].[aspnet_Users].[ApplicationId],
            [dbo].[aspnet_Users].[UserName],
            [dbo].[aspnet_Users].[MobileAlias],
            [dbo].[aspnet_Users].[IsAnonymous],
            [dbo].[aspnet_Users].[LastActivityDate]
  FROM [dbo].[aspnet_Membership] INNER JOIN [dbo].[aspnet_Users]
      ON [dbo].[aspnet_Membership].[UserId] = [dbo].[aspnet_Users].[UserId]
GO
/****** Object:  View [dbo].[vw_aspnet_WebPartState_Paths]    Script Date: 09/27/2007 23:53:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_WebPartState_Paths]
  AS SELECT [dbo].[aspnet_Paths].[ApplicationId], [dbo].[aspnet_Paths].[PathId], [dbo].[aspnet_Paths].[Path], [dbo].[aspnet_Paths].[LoweredPath]
  FROM [dbo].[aspnet_Paths]
GO
/****** Object:  View [dbo].[vw_aspnet_Roles]    Script Date: 09/27/2007 23:53:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_Roles]
  AS SELECT [dbo].[aspnet_Roles].[ApplicationId], [dbo].[aspnet_Roles].[RoleId], [dbo].[aspnet_Roles].[RoleName], [dbo].[aspnet_Roles].[LoweredRoleName], [dbo].[aspnet_Roles].[Description]
  FROM [dbo].[aspnet_Roles]
GO
/****** Object:  View [dbo].[vw_aspnet_Users]    Script Date: 09/27/2007 23:53:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_Users]
  AS SELECT [dbo].[aspnet_Users].[ApplicationId], [dbo].[aspnet_Users].[UserId], [dbo].[aspnet_Users].[UserName], [dbo].[aspnet_Users].[LoweredUserName], [dbo].[aspnet_Users].[MobileAlias], [dbo].[aspnet_Users].[IsAnonymous], [dbo].[aspnet_Users].[LastActivityDate]
  FROM [dbo].[aspnet_Users]
GO
/****** Object:  View [dbo].[vw_aspnet_UsersInRoles]    Script Date: 09/27/2007 23:53:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_UsersInRoles]
  AS SELECT [dbo].[aspnet_UsersInRoles].[UserId], [dbo].[aspnet_UsersInRoles].[RoleId]
  FROM [dbo].[aspnet_UsersInRoles]
GO
/****** Object:  View [dbo].[vw_aspnet_Profiles]    Script Date: 09/27/2007 23:53:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_Profiles]
  AS SELECT [dbo].[aspnet_Profile].[UserId], [dbo].[aspnet_Profile].[LastUpdatedDate],
      [DataSize]=  DATALENGTH([dbo].[aspnet_Profile].[PropertyNames])
                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesString])
                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesBinary])
  FROM [dbo].[aspnet_Profile]
GO
/****** Object:  View [dbo].[vw_aspnet_Applications]    Script Date: 09/27/2007 23:53:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_Applications]
  AS SELECT [dbo].[aspnet_Applications].[ApplicationName], [dbo].[aspnet_Applications].[LoweredApplicationName], [dbo].[aspnet_Applications].[ApplicationId], [dbo].[aspnet_Applications].[Description]
  FROM [dbo].[aspnet_Applications]
GO
/****** Object:  View [dbo].[vw_search_items]    Script Date: 09/27/2007 23:53:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_search_items]
AS
/*-- BLOGS --*/
SELECT     
	1 AS linktype, 
	'?pid=blg_blogviewer&id='+Convert(nvarchar(40), blogid) as urlparams, 
	'blog' as item, 
	blogid as itemid, 
	title, 
	body AS summary, 
	body, 0 AS spotlight, 
	createdate, 
	IsNull(private, 0) as private, 
	dbo.fn_get_username(userid) as createdby,
	convert(nvarchar(40), userid) as userid,
	IsApproved = 1
FROM         dbo.blog
UNION ALL
/*-- EVENTS --*/
SELECT     
	2 AS linktype, 
	'?pid=eve_eventviewer&id='+Convert(nvarchar(40), EventID) as urlparams,  
	'event' as item, 
	EventID as itemid, 
	EventTitle AS title, 
	EventDetails AS summary, 
	EventDetails AS body, 
	0 AS spotlight, 
	EventDate as createdate, 
	IsNull(private, 0) as private, 
	dbo.fn_get_username(userid) as createdby,
	convert(nvarchar(40), userid) as userid,
	IsApproved = 1
FROM         dbo.events
UNION ALL
/*-- NEWS --*/
SELECT     
	3 AS linktype, 
	'?pid=main&view=1&id='+Convert(nvarchar(40), newsid) as urlparams,  
	'news' as item, newsid as itemid, 
	title, 
	body AS summary, 
	body, 
	0 AS spotlight,
	createdate, 
	0 as private, 
	dbo.fn_get_username(userid) as createdby,
	IsNull(convert(nvarchar(40), userid), '') as userid,
	IsApproved = 1
FROM         dbo.news
UNION ALL
/*-- PAGES/ARTICLES --*/
SELECT     
	4 AS linktype, 
	'?pid=viewer&id='+Convert(nvarchar(40),  pfd.pageid) as urlparams,  
	'page' as item, 
	pfd.pageid as itemid, 
	page.title, 
	summ.summary, 
	dbo.fn_search_page(pfd.pageid) AS body, 
	ISNULL(page.spotlight, 0) AS spotlight,
	page.createdate, 
	(CASE page.iv_access_type WHEN 2 THEN 0 ELSE page.iv_access_type END) as private, 
	dbo.fn_get_username(page.userid) as createdby,
	IsNull(convert(nvarchar(40), page.userid), '') as userid,
	IsApproved = 1
FROM         (SELECT DISTINCT pageid FROM dbo.page_final_data) AS pfd
	LEFT OUTER JOIN dbo.page AS page ON page.pageid = pfd.pageid 
	LEFT OUTER JOIN dbo.page_summary AS summ ON summ.pageid = page.pageid 
	LEFT OUTER JOIN dbo.int_value AS iv_accesstype ON iv_accesstype.int_value = page.iv_access_type AND iv_accesstype.int_name = 'iv_access_type' 
	LEFT OUTER JOIN dbo.int_value AS iv_level ON iv_level.int_value = page.iv_level AND iv_level.int_name = 'iv_level'
WHERE page.iv_status = 6
UNION ALL
/*-- RESOURCES --*/
SELECT    
	5 AS linktype, 
	'?pid=res_download&resourceid='+Convert(nvarchar(40), resourceid) as urlparams,  
	'resource' as item, 
	resourceid as itemid,  
	ISNULL(resourcetitle, resourcename) AS title, 
	resourcedesc AS summary, 
	resourcedesc AS body, 
	0 AS spotlight, 
	createdate, 
	IsNull(private, 0) as private, 
	dbo.fn_get_username(userid) as createdby,
	IsNull(convert(nvarchar(40), userid), '') as userid,
	IsApproved = 1
FROM         dbo.resources
UNION ALL
/*-- FORUM POSTS --*/
SELECT
	6 as linktype,
	'?pid=for_post&topicid='+Convert(nvarchar(40), t.topicid)+'&forumid='+Convert(nvarchar(40), f.forumid)+'' urlparams,
	'forum' as item,
	t.topicid as itemid,
	t.topicname as title,
	dbo.fn_combine_posts(t.topicid) as summary,
	dbo.fn_combine_posts(t.topicid) as body,
	0 as spotlight,	 
	(CASE WHEN  t.created <= t.LastPostDate THEN t.LastPostDate ELSE t.created END) as createdate,
	0 as private,
	dbo.fn_get_username(t.lastuserid) as createdby,
	IsNull(convert(nvarchar(40), t.lastuserid), '') as userid,
	IsNull(t.isapproved, 1) as IsApproved
FROM
	forum_topic t
JOIN forum_forum as f on f.ForumID = t.ForumID
UNION ALL
/*-- POLLS --*/
SELECT     
	7 AS linktype, 
	'?pid=pol_pollviewer&id='+Convert(nvarchar(40), pollid) as urlparams,  
	'poll' as item, 
	pollid as itemid, 
	pollquestion, 
	pollquestion AS summary, 
	pollquestion, 
	0 AS spotlight,
	createdate, 
	0 as private, 
	dbo.fn_get_username(userid) as createdby,
	IsNull(convert(nvarchar(40), userid), '') as userid,
	IsApproved = 1
FROM         dbo.pollquestions
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4[30] 2[40] 3) )"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2[66] 3) )"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 5
   End
   Begin DiagramPane = 
      PaneHidden = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      PaneHidden = 
      Begin ColumnWidths = 5
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_search_items'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_search_items'
GO
/****** Object:  View [dbo].[vw_search_tags]    Script Date: 09/27/2007 23:53:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_search_tags]
AS

/*-- 
Any changes to the returned fields here has to be also add
to the vw_searc_items, as this built from that view
--*/

SELECT
	t.tagname, 
	urlparams,
	linktype, 
	item, 
	vsi.itemid,
	title,
	summary,
	body,
	spotlight,
	createdate,
	private,
	vsi.userid,
	dbo.fn_comment_count(vsi.itemid) as comments,
	dbo.fn_view_count(vsi.itemid) as views,
	IsNull(dbo.fn_rank_count(vsi.itemid), 0) as rank,
	dbo.fn_get_username(vsi.userid) as createdby,
	IsApproved
FROM vw_search_items as vsi
	join tags as t on t.itemid = vsi.itemid
GO
